from flask import Flask, render_template, request, redirect, url_for, send_file
import sqlite3
import csv
import os

app = Flask(__name__)
DATABASE = os.path.join(os.path.dirname(os.path.abspath(__file__)),"carbon_history.db")
CSVFILE = os.path.join(os.path.dirname(os.path.abspath(__file__)),"carbon_history.csv")
# DATABASE = 'carbon_history.db'
# csv_file = 'carbon_history.csv'

# 初始化数据库
def init_db():
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS carbon_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                time TEXT NOT NULL,
                process_emission REAL NOT NULL,
                energy_consumption REAL NOT NULL,
                resource_consumption REAL NOT NULL,
                process_factor REAL NOT NULL,
                energy_factor REAL NOT NULL,
                resource_factor REAL NOT NULL,
                total_carbon REAL NOT NULL
            );
        ''')
        conn.commit()

# 添加历史记录
def add_history_record(data):
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO carbon_history (time, process_emission, energy_consumption, resource_consumption, process_factor, energy_factor, resource_factor, total_carbon)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?);
        ''', data)
        conn.commit()

# 获取所有历史记录
def get_all_history_records():
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM carbon_history ORDER BY id DESC;')
        return cursor.fetchall()

# 清除所有历史记录
def clear_history_records():
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('DELETE FROM carbon_history;')
        conn.commit()

# 首页路由
@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        # 获取表单数据
        process_emission = float(request.form['processEmission'])
        energy_consumption = float(request.form['energyConsumption'])
        resource_consumption = float(request.form['resourceConsumption'])
        process_factor = float(request.form['processFactor'])
        energy_factor = float(request.form['energyFactor'])
        resource_factor = float(request.form['resourceFactor'])

        # 计算总碳排放量
        total_carbon = (process_emission * process_factor) + \
                       (energy_consumption * energy_factor) + \
                       (resource_consumption * resource_factor)

        # 保存历史记录
        timestamp = request.form['timestamp']
        data = (timestamp, process_emission, energy_consumption, resource_consumption, process_factor, energy_factor, resource_factor, total_carbon)
        add_history_record(data)

        return redirect(url_for('index'))

    # 获取所有历史记录
    history_records = get_all_history_records()
    return render_template('index.html', history_records=history_records)

# 清除历史记录路由
@app.route('/clear', methods=['POST'])
def clear_history():
    clear_history_records()
    return redirect(url_for('index'))

# 保存历史记录为 CSV 文件路由
@app.route('/save', methods=['POST'])
def save_history():
    history_records = get_all_history_records()
    csv_file = CSVFILE
    with open(csv_file, 'w', newline='', encoding='utf-8') as file:
        writer = csv.writer(file)
        writer.writerow(['时间', '工艺排放 (吨)', '能源消耗 (吨)', '资源消耗 (吨)', '工艺排放因子', '能源消耗因子', '资源消耗因子', '总碳排放量 (吨)'])
        writer.writerows(history_records)
    return send_file(csv_file, as_attachment=True)

if __name__ == '__main__':
    init_db()
    app.run(debug=True)